package com.css.fxfzypg.modules.earthquakedisasterloss.repository.Impl;

import com.css.fxfzypg.modules.baseData.entity.PbeEntity;
import com.css.fxfzypg.modules.earthquakedisasterloss.entity.PlantPbe;
import com.css.fxfzypg.modules.earthquakedisasterloss.repository.YpgResultPbeRepository;
import com.css.fxfzypg.util.JdbcUtil;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.List;
import java.util.Map;

/**
 * @Description: fxfzypg
 * @Author: lhl
 * @CreateDate: 2022/11/4 9:16
 */
@Repository
public class YpgResultPbeRepositoryImpl implements YpgResultPbeRepository {
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Override
    public int savePlantPbe(PlantPbe entity) {
        String sql = "insert into ypg_result_plant_pbe(id,pde_id,earthquake_id,task_id,remark,influenceflied_id," +
                "plant_person,magnitude,angle,dz_intensity,sort,longitude,latitude,geom)" +
                "values(:id,:pdeId,:earthquakeId,:taskId,:remark,:influencefliedId,:plantPerson,:magnitude,:angle," +
                ":dzIntensity,:sort,:longitude,:latitude,st_geometryfromtext(:geomText,4490))";
        SqlParameterSource source = new BeanPropertySqlParameterSource(entity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    public List<PlantPbe> getByTaskId(String taskId) {
        String sql="select * from ypg_result_plant_pbe where task_id = ?";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PlantPbe.class),taskId);
    }

    @Override
    public int delPlantPbe(String taskId) {
        /*String sql="delete from ypg_result_plant_pbe where task_id = ?";
        return jdbcTemplate.update(sql, taskId);*/
//        String sql = "drop table ypg_result_plant_pbe_" + taskId;
        String sql = "select DropGeometryTable('ypg_result_plant_pbe_"+taskId+"')";//空间表删除
        JdbcUtil.executeSql(sql);
        return 0;
    }

    @Override
    public List<PbeEntity> getPbeByTaskId(String taskId) {
        /*String sql ="SELECT *,st_asText(geom) geomText FROM data_pbe T WHERE ST_Intersects (T.geom,(SELECT st_union " +
                "(A.geom,(SELECT b.geom FROM ypg_preassess_task b WHERE ID = '"+taskId+"')) AS geom FROM" +
                "(SELECT st_union (C.geom) AS geom FROM setting_seismic_influenceflied C WHERE C.task_id = '"+taskId+"') A " +
                ") ) AND isdelete = '0' order by T.county";*/
        String sql ="SELECT *,st_asText(geom) geomText FROM data_pbe T " +
                "where (ST_Intersects(T.geom,(select geom from ypg_preassess_task where id='"+taskId+"')) " +
                "or " +
                "ST_Intersects(T.geom,(select st_union(n.geom) as geom from setting_seismic_influenceflied n where n.task_id='"+taskId+"'))) " +
                "AND isdelete = '0' order by T.county";
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PbeEntity.class));
    }

    @Override
    public List<PlantPbe> getPbeByDzIntensity(String taskId, String dzIntensity) {
        String sql="select *,ST_AsText(geom) as geomText  from ypg_result_plant_pbe where task_id = ?  " ;
        if(!"".equals(dzIntensity)){
            sql+=" and dz_intensity = ?";
            return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PlantPbe.class), taskId,dzIntensity);
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(PlantPbe.class), taskId);
    }

    @Override
    public List<Map<String, Object>> getPlantPbeByTaskId(String taskId,String range) {
        String sql="";
        if ("0".equals(range)){
            sql ="select a.pde_id pbeId,st_area(st_intersection(ST_MakeValid(a.geom), ST_MakeValid(b.geom))) as area,a.plant_person plantPerson," +
                    "b.fault_num sort,b.earthquake_level earthquakeLevel,b.angle angle,b.source_earthquake_id earthquakeId" +
                    " from ypg_result_plant_pbe a left join (select b.geom,b.task_id,b.dz_intensity,c.fault_num,c.earthquake_level," +
                    "c.angle,b.source_earthquake_id from setting_seismic_details c left join setting_seismic_influenceflied b on c.id=b.source_earthquake_id " +
                    " where b.task_id='"+taskId+"' and c.is_delete='0') b on a.task_id=b.task_id " +
                    " where a.dz_intensity=b.dz_intensity and st_intersects(a.geom,b.geom)  " +
                    " order by a.pde_id,b.source_earthquake_id";
        }else {
            sql ="select a.pde_id pbeId,st_area(st_intersection(ST_MakeValid(a.geom), ST_MakeValid(b.geom))) as area,a.plant_person plantPerson," +
                    "b.fault_num sort,b.earthquake_level earthquakeLevel,b.angle angle,b.source_earthquake_id earthquakeId" +
                    " from ypg_result_plant_pbe a left join (select b.geom,b.task_id,b.dz_intensity,c.fault_num,c.earthquake_level," +
                    "c.angle,b.source_earthquake_id from setting_seismic_details c left join setting_seismic_influenceflied b on c.id=b.source_earthquake_id " +
                    " where b.task_id='"+taskId+"' and c.is_delete='0') b on a.task_id=b.task_id " +
                    " where a.dz_intensity=b.dz_intensity and st_intersects(a.geom,b.geom) " +
                    " AND st_intersects ( a.geom, (SELECT geom from ypg_preassess_task where id ='"+taskId+"') )  " +
                    " order by a.pde_id,b.source_earthquake_id";
        }

        return  jdbcTemplate.queryForList(sql);
    }

    @Override
    @Transactional(propagation= Propagation.REQUIRES_NEW)
    public int savePlantPbeList(List<PlantPbe> plantPbeList) {
        try {
            String sql = "insert into ypg_result_plant_pbe(id,pde_id,earthquake_id,task_id,remark,influenceflied_id," +
                    "plant_person,magnitude,angle,dz_intensity,sort,longitude,latitude,geom)" +
                    "values(:id,:pdeId,:earthquakeId,:taskId,:remark,:influencefliedId,:plantPerson,:magnitude,:angle," +
                    ":dzIntensity,:sort,:longitude,:latitude,st_geometryfromtext(:geomText,4490))";
            int[] ints = namedParameterJdbcTemplate.batchUpdate(sql, SqlParameterSourceUtils.createBatch(plantPbeList));
            return ints.length;
        }catch (Exception e){
            throw new RuntimeException("批量插入失败", e);
        }

    }

    @Override
    public void createResultPbeFqRel(String taskId) {
        String createSql = "create table ypg_result_plant_pbe_" + taskId +
                " PARTITION OF ypg_result_plant_pbe for values in ('"+taskId+"')";
        JdbcUtil.executeSql(createSql);
    }
}
